﻿using CmccTool.KPI.Services;
using Microsoft.Data.Sqlite;
using MiniExcelLibs;
using SQLitePCL;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;

namespace CmccTool.KPI.View
{
    public partial class KpiQuery : UserControl
    {
        #region SQL扩展方法

        /// <summary>
        /// 更新SQL语句
        /// </summary>
        private string SqlQuery
        {
            get
            {
                if (_tableMapping.TryGetValue(DbTable, out var mapping))
                {
                    StringBuilder sb = new();
                    // 拼接查询语句
                    sb.Append("SELECT * FROM ");
                    sb.Append(mapping.tableName);
                    sb.Append($@" WHERE {mapping.dateField} >='{dateSta.ToString("yyyy-MM-dd")}' and {mapping.dateField}<='{dateEnd.ToString("yyyy-MM-dd")}'");
                    // 拼接查询条件 CGIList
                    switch (CGIList.Count)
                    {
                        case 0:
                            if (!string.IsNullOrEmpty(CGI))
                            {
                                sb.Append($@" AND {mapping.CGIField} {(CGI.Contains('%') ? "LIKE" : "=")} '{CGI}'");
                            }
                            break;
                        case 1:
                            sb.Append($@" AND {mapping.CGIField} = '{CGIList[0]}'");
                            break;
                        case > 1:

                            string[] CGIArray = CGIList.ToArray();
                            string inClause = string.Join("','", CGIArray);
                            sb.Append($@" AND {mapping.CGIField} IN ('{inClause}')");
                            break;
                    }

                    // 拼接查询条件 ENIList
                    switch (ENIList.Count)
                    {
                        case 0:
                            if (!string.IsNullOrEmpty(ENI))
                            {
                                sb.Append($@" AND {mapping.ENIField} {(ENI.Contains('%') ? "LIKE" : "=")} '{ENI}'");
                            }
                            break;
                        case 1:
                            sb.Append($@" AND {mapping.ENIField} = '{ENIList[0]}'");
                            break;
                        case > 1:
                            string[] ENIArray = ENIList.ToArray();
                            string inClause = string.Join("','", ENIArray);
                            sb.Append($@" AND {mapping.ENIField} IN ('{inClause}')");
                            break;
                    }

                    if (!string.IsNullOrEmpty(CellName))
                    {
                        string inClause = $@" select eni from gccgiinfo where 工参小区 like '%{CellName}%' ";
                        sb.Append($@" AND {mapping.ENIField} in ({inClause}) "); 
                    }
                    // 拼接排序和限制
                    sb.Append($" ORDER BY {mapping.dateField} desc");
                    sb.Append($" limit 1000"); // 限制返回行数，防止数据过多
                    return sb.ToString();
                }
                else
                {
                    return $"未知的数据库表{DbTable}";
                }


            }
        }

        /// <summary>
        /// 根据输入的CGI列表，处理并补全缺失的前缀，返回标准化的CGI列表
        /// </summary>
        /// <param name="cgiList">CGI列表</param>
        /// <returns></returns>
        public static List<string> ProcessCGIList(string cgiList)
        {
            // 正则表达式模式：匹配"460-00-数字-数字"或"数字-数字"
            string pattern = @"\b(?:460-00-)?\d+-\d+\b";
            MatchCollection matches = Regex.Matches(cgiList, pattern);

            // 处理每个匹配项：补充缺失的"460-00-"前缀
            var processedItems = new List<string>();
            foreach (Match match in matches)
            {
                string item = match.Value;
                // 检查是否已包含"460-00-"前缀，若缺失则补充
                if (!item.StartsWith("460-00-"))
                {
                    item = "460-00-" + item;
                }
                processedItems.Add(item);
            }

            // 根据匹配数量返回结果
            return processedItems;
        }


        /// <summary>
        /// 根据输入的ENI列表，处理并补全缺失的前缀，返回标准化的ENI列表
        /// </summary>
        /// <param name="cgiList">CGI列表</param>
        /// <returns></returns>
        public static List<string> ProcessEniList(string ENIList)
        {
            // 正则表达式模式：匹配"460-00-数字-数字"或"数字-数字"
            string pattern = @"\d{5-7}";
            MatchCollection matches = Regex.Matches(ENIList, pattern);

            // 处理每个匹配项：补充缺失的"460-00-"前缀
            var processedItems = new List<string>();
            foreach (Match match in matches)
            {
                string item = match.Value;
                processedItems.Add(item);
            }

            // 根据匹配数量返回结果
            return processedItems;
        }

        /// <summary>
        /// 数据表字段名映射
        /// </summary>
        private static readonly Dictionary<string, (string tableName, string dateField, string CGIField, string ENIField, string CellNameField)>
            _tableMapping = new(StringComparer.OrdinalIgnoreCase)
            {
                {"新网优",("KpiXwy","date","CGI","ENI","工参小区") },
                {"爱立信OMC",("KpiOmc","date","CGI","ENI","工参小区") },
                {"4G零低",("kpiXwyLteLd","date","CGI","ENI","小区名") },
                {"5G零低",("kpiXwyNrLd","date","CGI","ENI","小区名") },
                {"5G低效",("KpiXwyNrDx","date","CGI","ENI","小区名") },
                {"质差小区",("ErrCell","开始日期","CGI","ENI","工参小区") },
                {"问题基站",("ErrNode","开始日期","CGI","ENI","工参基站") }
            };
        #endregion

        #region 窗体初始化
        public KpiQuery()
        {
            InitializeComponent();
            // 设置DataGridView为虚拟模式（大容量数据时可选）
            dataGridView.VirtualMode = true;
            dataGridView.RowPrePaint += (s, e) => { /* 禁用行自绘提升性能 */ };
            SetDoubleBuffered(dataGridView);
            // 初始化组件
            ComboxDbTable.Items.AddRange(["新网优", "爱立信OMC", "4G零低", "5G零低", "5G低效", "质差小区", "问题基站"]);
            ComboxDbTable.SelectedIndex = 0; // 默认选择第一个

            // 初始化数据服务
            _dataService = new KpiQueryDbAsync(AppConfig.dbPath);

            // 设置初始时间
            datePickSta.Value = dateSta;
            datePickEnd.Value = dateEnd;
        }

        // 双缓冲技术
        private static void SetDoubleBuffered(Control control)
        {
            if (SystemInformation.TerminalServerSession) return;
            var prop = typeof(Control).GetProperty("DoubleBuffered",
                System.Reflection.BindingFlags.NonPublic |
                System.Reflection.BindingFlags.Instance);
            prop?.SetValue(control, true, null);
        }
        #endregion

        #region 控件事件，内容变化动态生成sql语句
        // 查询条件-变量
        string CGI = string.Empty;
        List<string> CGIList = new();
        List<string> ENIList = new();
        string ENI = string.Empty;
        string CellName = string.Empty;
        string DbTable = "新网优";
        DateTime dateSta = DateTime.Today.AddYears(-1);
        DateTime dateEnd = DateTime.Today;
        private void UpdateQuery()
        {
            TxbSql.Text = SqlQuery;

        }

        private void TxbCGI_TextChanged(object sender, EventArgs e)
        {
            string originalText = TxbCGI.Text;
            string trimmedText = originalText.Trim();

            if (originalText != trimmedText)
            {
                TxbCGI.Text = trimmedText;
                TxbCGI.SelectionStart = trimmedText.Length; // 保持光标在末尾
            }
            else
            {
                CGI = trimmedText;
                CGIList = ProcessCGIList(CGI);
                UpdateQuery();
            }
        }

        private void TxbENI_TextChanged(object sender, EventArgs e)
        {
            string originalText = TxbENI.Text;
            string trimmedText = originalText.Trim();

            if (originalText != trimmedText)
            {
                TxbENI.Text = trimmedText;
                TxbENI.SelectionStart = trimmedText.Length; // 保持光标在末尾
            }
            else
            {
                ENI = trimmedText;
                ENIList = ProcessEniList(ENI);
                UpdateQuery();
            }
        }

        private void TxbName_TextChanged(object sender, EventArgs e)
        {
            string originalText = TxbName.Text;
            string trimmedText = originalText.Trim();

            if (originalText != trimmedText)
            {
                TxbName.Text = trimmedText;
                TxbName.SelectionStart = trimmedText.Length; // 保持光标在末尾
            }
            else
            {
                CellName = trimmedText;
                UpdateQuery();
            }

        }

        private void ComboxDbTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            DbTable = ComboxDbTable.SelectedItem?.ToString() ?? "新网优";
            UpdateQuery();
        }
        private void DatePickSta_ValueChanged(object sender, EventArgs e)
        {
            dateSta = datePickSta.Value.Date;
        }

        private void DatePickEnd_ValueChanged(object sender, EventArgs e)
        {
            dateEnd = datePickEnd.Value.Date;
            UpdateQuery();
        }


        #endregion

        #region 查询功能

        private readonly KpiQueryDbAsync _dataService;
        private async void BtnStartQuery_Click(object sender, EventArgs e)
        {
            // 老版本查询方法
            //await LoadDataAsync(TxbSql.Text.Trim(), dataGridView);

            // 新版本查询方法
            // 在窗体初始化中添加
            //_dataService = new KpiDataService(AppConfig.dbPath);

            if (!File.Exists(AppConfig.dbPath)) { MessageBox.Show($"数据库不存在{AppConfig.dbPath},{Environment.NewLine}请先导入数据，{Environment.NewLine}路径为:KPI->KPI导入", "Error"); return; }

            var tSta = DateTime.Now;

            // 执行查询
            DataTable data = await _dataService.ExecuteQueryAsync(TxbSql.Text.Trim());

            // 绑定数据
            //dataGridView.DataSource = data;
            this.BeginInvoke((Action)(() =>
            {
                // 先解除当前数据绑定
                dataGridView.DataSource = null;
                // 清除所有行
                dataGridView.Rows.Clear();

                // 设置新的数据源
                dataGridView.DataSource = data;
                // 自动调整列宽
                dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
                // 更新状态标签
                var tCode = DateTime.Now - tSta;
                lblStatus.Text = $"{DateTime.Now:yyyy-MM-dd HH:mm:ss}:查询完成，共返回 {data.Rows.Count} 行数据,耗时{string.Format("{0:00}:{1:00}:{2:00}.{3:000}", (int)tCode.TotalHours, tCode.Minutes, tCode.Seconds, tCode.Milliseconds)}";

                List<string> hideFields = ["Keyid", "IsNew"];
                foreach (string field in hideFields)
                {
                    var hideField = dataGridView.Columns[field];
                    if (hideField != null)
                    {
                        hideField.Visible = false;
                    }
                }


            }));
        }

        protected override void OnHandleDestroyed(EventArgs e)
        {
            _dataService.Dispose();
            base.OnHandleDestroyed(e);
        }

        /* 老版本查询方法
        private CancellationTokenSource? _cancellationTokenSource;
        private readonly string _connectionString = $"Data Source={AppConfig.dbPath};";
        // 异步加载数据方法
        public async Task LoadDataAsync(string sqlQuery, DataGridView dataGridView)
        {
            // 取消之前的加载任务（如果存在）
            _cancellationTokenSource?.Cancel();
            _cancellationTokenSource = new CancellationTokenSource();
            var token = _cancellationTokenSource.Token;

            try
            {
                // 清空现有数据（线程安全）
                ClearDataGridView(dataGridView);

                // 启动后台加载任务
                DataTable data = await Task.Run(() => FetchDataFromDatabase(sqlQuery, token), token);

                // 检查是否取消请求
                if (token.IsCancellationRequested) return;

                // 绑定数据到DataGridView
                this.BeginInvoke((Action)(() =>
                {
                    dataGridView.DataSource = data;
                    dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
                }));
            }
            catch (OperationCanceledException)
            {
                // 正常取消，无需处理
            }
            catch (Exception ex)
            {
                this.BeginInvoke((Action)(() =>
                    MessageBox.Show($"加载失败: {ex.Message}")));
            }
        }

        // 数据库查询方法
        private DataTable FetchDataFromDatabase(string sql, CancellationToken token)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            using var command = new SqliteCommand(sql, connection);
            using var reader = command.ExecuteReader();

            // 创建数据表结构
            var dataTable = new DataTable();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                // 检测取消请求
                token.ThrowIfCancellationRequested();
                dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }

            // 填充数据
            while (reader.Read())
            {
                token.ThrowIfCancellationRequested();
                DataRow row = dataTable.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[i] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
                }
                dataTable.Rows.Add(row);
            }
            return dataTable;
        }

        // 清空DataGridView（线程安全）
        private void ClearDataGridView(DataGridView dataGridView)
        {
            if (dataGridView.InvokeRequired)
            {
                //dataGridView.BeginInvoke((Action)ClearDataGridView);
                dataGridView.BeginInvoke(new Action<DataGridView>(ClearDataGridView), dataGridView);
                return;
            }

            if (dataGridView.DataSource != null)
            {
                // 保留当前选中行/滚动位置
                var firstVisibleRow = dataGridView.FirstDisplayedScrollingRowIndex;
                var selectedRow = dataGridView.CurrentCell?.RowIndex;

                dataGridView.DataSource = null;

                // 恢复位置
                if (firstVisibleRow >= 0 && dataGridView.Rows.Count > firstVisibleRow)
                    dataGridView.FirstDisplayedScrollingRowIndex = firstVisibleRow;
                if (selectedRow.HasValue && selectedRow >= 0 && dataGridView.Rows.Count > selectedRow.Value)
                    dataGridView.CurrentCell = dataGridView.Rows[selectedRow.Value].Cells[0];
            }
        }

        // 取消加载
        public void CancelLoading()
        {
            _cancellationTokenSource?.Cancel();
        }

        // 窗体关闭时清理资源
        protected override void OnHandleDestroyed(EventArgs e)
        {
            CancelLoading();
            base.OnHandleDestroyed(e);
        }
        */
        #endregion

        #region 导出功能
        private void BtmExport_Click(object sender, EventArgs e)
        {
            string fName = $"KpiExport_{DbTable}";
            if (!string.IsNullOrEmpty(CGI))
            {
                fName += $"_CGI-{CGI.Replace("%", "^")}";
            }
            else if (!string.IsNullOrEmpty(CellName))
            {
                fName += $"_Name-{CellName.Replace("%", "^")}";
            }
            else if (!string.IsNullOrEmpty(ENI))
            {
                fName += $"_ENI-{ENI.Replace("%", "^")}";
            }

            string f = Path.Combine(AppConfig.GetSafeAppDataPath(), "exp", fName + "_" + DateTime.Now.ToString("yyyy-MM-dd hh_mm_ss") + ".xlsx");
            if (dataGridView.DataSource is not DataTable dataTable || dataTable.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可导出");
                return;
            }
            else
            {
                MiniExcel.SaveAs(f, dataTable);
                lblStatus.Text = $"{DateTime.Now:yyyy-MM-dd hh:mm:ss}: 导出完成，共计 {dataTable.Rows.Count} 行数据，导出文件：{f}";
            }
        }
        #endregion

    }
}
